 1.元数据管理工具Atlas(扩展)之与电商业务集成
   
   开发（建库、建表） => 导入数据 => 执行Hive脚本
   导入Hive的血缘关系
   
   电商业务建表语句（可省略）：
   -- 创建DataBases;
   CREATE DATABASE ODS;
   CREATE DATABASE DIM;
   CREATE DATABASE DWD;
   CREATE DATABASE DWS;
   CREATE DATABASE ADS;
   
   -- 创建ODS表
DROP TABLE IF EXISTS `ods.ods_trade_orders`;
CREATE EXTERNAL TABLE `ods.ods_trade_orders`(
`orderid` int,
`orderno` string,
`userid` bigint,
`status` tinyint,
`productmoney` decimal(10,0),
`totalmoney` decimal(10,0),
`paymethod` tinyint,
`ispay` tinyint,
`areaid` int,
`tradesrc` tinyint,
`tradetype` int,
`isrefund` tinyint,
`dataflag` tinyint,
`createtime` string,
`paytime` string,
`modifiedtime` string)
COMMENT '订单表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by ','
location '/user/data/trade.db/orders/';
   
   DROP TABLE IF EXISTS `ods.ods_trade_order_product`;
CREATE EXTERNAL TABLE `ods.ods_trade_order_product`(
`id` string,
`orderid` decimal(10,2),
`productid` string,
`productnum` string,
`productprice` string,
`money` string,
`extra` string,
`createtime` string)
COMMENT '订单明细表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by ','
location '/user/data/trade.db/order_product/';
   
   DROP TABLE IF EXISTS `ods.ods_trade_product_info`;
CREATE EXTERNAL TABLE `ods.ods_trade_product_info`(
`productid` bigint,
`productname` string,
`shopid` string,
`price` decimal(10,0),
`issale` tinyint,
`status` tinyint,
`categoryid` string,
`createtime` string,
`modifytime` string)
COMMENT '产品信息表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by ','
location '/user/data/trade.db/product_info/';
   
   DROP TABLE IF EXISTS `ods.ods_trade_product_category`;
CREATE EXTERNAL TABLE `ods.ods_trade_product_category`(
`catid` int,
`parentid` int,
`catname` string,
`isshow` tinyint,
`sortnum` int,
`isdel` tinyint,
`createtime` string,
`level` tinyint)
COMMENT '产品分类表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by ','
location '/user/data/trade.db/product_category';
   
   DROP TABLE IF EXISTS `ods.ods_trade_shops`;
CREATE EXTERNAL TABLE `ods.ods_trade_shops`(
`shopid` int,
`userid` int,
`areaid` int,
`shopname` string,
`shoplevel` tinyint,
`status` tinyint,
`createtime` string,
`modifytime` string)
COMMENT '商家店铺表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by ','
location '/user/data/trade.db/shops';
   
   DROP TABLE IF EXISTS `ods.ods_trade_shop_admin_org`;
CREATE EXTERNAL TABLE `ods.ods_trade_shop_admin_org`(
`id` int,
`parentid` int,
`orgname` string,
`orglevel` tinyint,
`isdelete` tinyint,
`createtime` string,
`updatetime` string,
`isshow` tinyint,
`orgType` tinyint)
COMMENT '商家地域组织表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by ','
location '/user/data/trade.db/shop_org/';
   
   DROP TABLE IF EXISTS `ods.ods_trade_payments`;
CREATE EXTERNAL TABLE `ods.ods_trade_payments`(
`id` string,
`paymethod` string,
`payname` string,
`description` string,
`payorder` int,
`online` tinyint)
COMMENT '支付方式表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by ','
location '/user/data/trade.db/payments/';
   
   -- 创建DIM表
   DROP TABLE IF EXISTS dim.dim_trade_product_cat;
create table if not exists dim.dim_trade_product_cat(
firstId int,      -- 一级商品分类id
firstName string, -- 一级商品分类名称
secondId int,     -- 二级商品分类Id
secondName string,-- 二级商品分类名称
thirdId int,      -- 三级商品分类id
thirdName string  -- 三级商品分类名称
)
partitioned by (dt string)
STORED AS PARQUET;
   
   drop table if exists dim.dim_trade_shops_org;
create table dim.dim_trade_shops_org(
shopid int,
shopName string,
cityId int,
cityName string ,
regionId int ,
regionName string
)
partitioned by (dt string)
STORED AS PARQUET;

   drop table if exists dim.dim_trade_payment;
create table if not exists dim.dim_trade_payment(
paymentId string, -- 支付方式id
paymentName string -- 支付方式名称
)
partitioned by (dt string)
STORED AS PARQUET;
    
   drop table if exists dim.dim_trade_product_info;
create table dim.dim_trade_product_info(
`productId` bigint,
`productName` string,
`shopId` string,
`price` decimal,
`isSale` tinyint,
`status` tinyint,
`categoryId` string,
`createTime` string,
`modifyTime` string,
`start_dt` string,
`end_dt` string
) COMMENT '产品表'
STORED AS PARQUET;
   
   -- 创建DWD表
   -- 订单事实表(拉链表)
   DROP TABLE IF EXISTS dwd.dwd_trade_orders;
create table dwd.dwd_trade_orders(
`orderId` int,
`orderNo` string,
`userId` bigint,
`status` tinyint,
`productMoney` decimal,
`totalMoney` decimal,
`payMethod` tinyint,
`isPay` tinyint,
`areaId` int,
`tradeSrc` tinyint,
`tradeType` int,
`isRefund` tinyint,
`dataFlag` tinyint,
`createTime` string,
`payTime` string,
`modifiedTime` string,
`start_date` string,
`end_date` string
) COMMENT '订单事实拉链表'
partitioned by (dt string)
STORED AS PARQUET;
   
   -- 创建DWS表
   DROP TABLE IF EXISTS dws.dws_trade_orders;
create table if not exists dws.dws_trade_orders(
orderid string,    -- 订单id
cat_3rd_id string, -- 商品三级分类id
shopid string,     -- 店铺id
paymethod tinyint, -- 支付方式
productsnum bigint,-- 商品数量
paymoney double,   -- 订单商品明细金额
paytime string     -- 订单时间
)
partitioned by (dt string)
STORED AS PARQUET;
   
   -- 订单明细表宽表
   DROP TABLE IF EXISTS dws.dws_trade_orders_w;
create table if not exists dws.dws_trade_orders_w(
orderid string,     -- 订单id
cat_3rd_id string,  -- 商品三级分类id
thirdname string,   -- 商品三级分类名称
secondname string,  -- 商品二级分类名称
firstname string,   -- 商品一级分类名称
shopid string,      -- 店铺id
shopname string,    -- 店铺名
regionname string,  -- 店铺所在大区
cityname string,    -- 店铺所在城市
paymethod tinyint,  -- 支付方式
productsnum bigint, -- 商品数量
paymoney double,    -- 订单明细金额
paytime string      -- 订单时间
)
partitioned by (dt string)
STORED AS PARQUET;

   -- 创建ADS表
   -- ADS层订单分析表
   DROP TABLE IF EXISTS ads.ads_trade_order_analysis;
create table if not exists ads.ads_trade_order_analysis(
areatype string,         -- 区域范围：区域类型（全国、大区、城市）
regionname string,       -- 区域名称
cityname string,         -- 城市名称
categorytype string,     -- 商品分类类型（一级、二级）
category1 string,        -- 商品一级分类名称
category2 string,        -- 商品二级分类名称
totalcount bigint,       -- 订单数量
total_productnum bigint, -- 商品数量
totalmoney double -- 支付金额
)
partitioned by (dt string)
row format delimited fields terminated by ',';

   使用Sqoop加载数据（可省略）：

sqoop import \
--connect jdbc:mysql://linux123:3306/ebiz \
--username hive \
--password 12345678 \
--target-dir /user/data/trade.db/orders/dt=2020-07-21/ \
--table lagou_trade_orders \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by ','

sqoop import \
--connect jdbc:mysql://linux123:3306/ebiz \
--username hive \
--password 12345678 \
--target-dir /user/data/trade.db/payments/dt=2020-07-21/ \
--table lagou_payments \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by ','

sqoop import \
--connect jdbc:mysql://linux123:3306/ebiz \
--username hive \
--password 12345678 \
--target-dir /user/data/trade.db/product_category/dt=2020-07-
21/ \
--table lagou_product_category \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by ','

sqoop import \
--connect jdbc:mysql://linux123:3306/ebiz \
--username hive \
--password 12345678 \
--target-dir /user/data/trade.db/product_info/dt=2020-07-21/ \
--table lagou_product_info \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by ','

sqoop import \
--connect jdbc:mysql://linux123:3306/ebiz \
--username hive \
--password 12345678 \
--target-dir /user/data/trade.db/order_product/dt=2020-07-21/
\
--table lagou_order_product \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by ','

sqoop import \
--connect jdbc:mysql://linux123:3306/ebiz \
--username hive \
--password 12345678 \
--target-dir /user/data/trade.db/shop_org/dt=2020-07-21/ \
--table lagou_shop_admin_org \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by ','

sqoop import \
--connect jdbc:mysql://linux123:3306/ebiz \
--username hive \
--password 12345678 \
--target-dir /user/data/trade.db/shops/dt=2020-07-21/ \
--table lagou_shops \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by ','
   
   alter table ods.ods_trade_orders add partition(dt='2020-07-21');
   alter table ods.ods_trade_payments add partition(dt='2020-07-21');
   alter table ods.ods_trade_product_category add partition(dt='2020-07-21');
   alter table ods.ods_trade_product_info add partition(dt='2020-07-21');
   alter table ods.ods_trade_order_product add partition(dt='2020-07-21');
   alter table ods.ods_trade_shop_admin_org add partition(dt='2020-07-21');
   alter table ods.ods_trade_shops add partition(dt='2020-07-21');
   
   电商业务脚本(省略了ODS层数据加载)：
   # 加载DIM层数据
   sh /data/lagoudw/script/trade/dim_load_product_cat.sh 2020-07-21
   sh /data/lagoudw/script/trade/dim_load_shop_org.sh 2020-07-21
   sh /data/lagoudw/script/trade/dim_load_payment.sh 2020-07-21
   sh /data/lagoudw/script/trade/dim_load_product_info.sh 2020-07-21
   
   # 加载DWD层数据
   sh /data/lagoudw/script/trade/dwd_load_trade_orders.sh 2020-07-21
   
   # 加载DWS层数据
   sh /data/lagoudw/script/trade/dws_load_trade_orders.sh 2020-07-21
   
   # 加载ADS层数据
   sh /data/lagoudw/script/trade/ads_load_trade_order_analysis.sh 2020-07-21
   
   创建 Classfication：order_analysis
   创建Glossary：ODS层 => 电商业务
   查看血缘关系 ads_trade_order_analysis：